package sf.database.util;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import sf.database.connection.ConnectionHelp;
import sf.database.connection.WrapConnection;
import sf.database.dao.DBContext;
import sf.database.dbinfo.ConnectInfo;
import sf.database.dialect.DBDialect;
import sf.database.dialect.DefaultDBDialect;
import sf.database.dialect.DialectUtils;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.Iterator;
import java.util.regex.Pattern;

public final class DBUtils {
    private static final Logger logger = LoggerFactory.getLogger(DBUtils.class);

    public static final String KEEP_ORDERBY = "/*keep orderby*/";

    public static void close(AutoCloseable obj) {
        try {
            if (obj != null) {
                obj.close();
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public static void closeQuietlyAutoCloseable(AutoCloseable obj) {
        try {
            if (obj != null) {
                obj.close();
            }
        } catch (Exception e) {

        }
    }

    public static void closeQuietly(Connection conn) {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
        }
    }

    public static void closeQuietly(Statement stmt) {
        try {
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException e) {
        }
    }

    public static void closeQuietly(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {

        }
    }


    /**
     * 根据已有的连接解析连接信息
     * @param conn
     * @return
     * @throws SQLException
     */
    public static ConnectInfo tryAnalyzeInfo(Connection conn) throws SQLException {
        DatabaseMetaData meta = conn.getMetaData();
        ConnectInfo info = new ConnectInfo();
        info.setUser(meta.getUserName());
        info.setUrl(meta.getURL());
        info.setDatabaseProductName(meta.getDatabaseProductName());
        info.setDatabaseProductVersion(meta.getDatabaseProductVersion());
        info.setDatabaseMajorVersion(meta.getDatabaseMajorVersion());
        info.setDriverVersion(meta.getDriverVersion());
        info.setDriverMajorVersion(meta.getDriverMajorVersion());
        // 解析，获得profile, 解析出数据库名等信息
        return info;
    }

    public static boolean doGetTableExist(Connection conn, String tabelName) {
        ResultSet rs = null;
        try {
            DatabaseMetaData metaData = conn.getMetaData();
            rs = metaData.getTables(null, null, tabelName.toUpperCase(), new String[]{"TABLE"});
            return rs.next();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeQuietly(rs);
        }
    }

    public static DBDialect doGetDialect(DataSource dataSource) {
        try {
            return doGetDialect(dataSource.getConnection(), true);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取连接的方言,如果无法获取连接的方言,则返回默认的方言.
     * @param conn
     * @param close 是否关闭
     * @return
     */
    public static DBDialect doGetDialect(Connection conn, boolean close) {
        try {
            if (conn instanceof WrapConnection && ((WrapConnection) conn).getDialect() != null) {
                return ((WrapConnection) conn).getDialect();
            }
            DatabaseMetaData dbmd = conn.getMetaData();
            String name = dbmd.getDatabaseProductName();
            String version = dbmd.getDatabaseProductVersion();
//            int majorVersion = dbmd.getDatabaseMajorVersion();
//            int minorVersion = dbmd.getDatabaseMinorVersion();
            DBDialect dialect = DialectUtils.getDialect(name, version);
            if (dialect.getQuoteChar() == 0) {
                dialect.accept(conn);
            }
            return dialect;
        } catch (SQLException e) {
//            throw new RuntimeException(e);
            logger.error("无法获取数据库连接方言!", e);
        } finally {
            if (close) {
                closeQuietly(conn);
            }
        }
        return DefaultDBDialect.instance;
    }

    /**
     * 获取数据库连接URL字符串
     * @param dataSource
     * @return 数据库连接URL字符串
     */
    public static String getJdbcURL(DataSource dataSource) {
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            return conn.getMetaData().getURL();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeQuietly(conn);
        }
    }

    /**
     * 获取数据库连接URL字符串,不关闭连接
     * @param conn
     * @return 数据库连接URL字符串
     */
    public static String getJdbcURL(Connection conn) {
        try {
            return conn.getMetaData().getURL();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取数据库连接URL字符串
     * @param conn
     * @param close
     * @return 数据库连接URL字符串
     */
    public static String getJdbcURL(Connection conn, boolean close) {
        try {
            return conn.getMetaData().getURL();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (close) {
                closeQuietly(conn);
            }
        }
    }

    /**
     * 获取数据库连接用户名称,不关闭连接
     * @param conn
     * @return 数据库连接用户名称
     */
    public static String getUserName(Connection conn) {
        try {
            return conn.getMetaData().getUserName();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取数据库连接用户名称
     * @param conn
     * @param close
     * @return 数据库连接用户名称
     */
    public static String getUserName(Connection conn, boolean close) {
        try {
            return conn.getMetaData().getUserName();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (close) {
                closeQuietly(conn);
            }
        }
    }

    /**
     * 获取上下文
     * @param conn
     * @return
     */
    public static DBContext doGetDBContext(Connection conn) {
        if (conn instanceof WrapConnection && ((WrapConnection) conn).getDbContext() != null) {
            return ((WrapConnection) conn).getDbContext();
        }
        return null;
    }

    public static String getIdentifierQuoteString(Connection conn) {
        try {
            return conn.getMetaData().getIdentifierQuoteString();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static String getDatabaseProductName(Connection conn) {
        try {
            return conn.getMetaData().getDatabaseProductName();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取版本号,针对sqlserver
     * @param conn
     * @return
     */
    public static String getDatabaseProductVersion(Connection conn) {
        try {
            String version = conn.getMetaData().getDatabaseProductVersion();
            return version;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static int getDatabaseMajorVersion(Connection conn) {
        try {
            return conn.getMetaData().getDatabaseMajorVersion();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static int getDatabaseMinorVersion(Connection conn) {
        try {
            return conn.getMetaData().getDatabaseMinorVersion();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * @param conn
     * @return
     */
    public static String getDriverName(Connection conn) {
        try {
            return conn.getMetaData().getDriverName();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取schema 不报错
     * @param conn
     * @return
     */
    public static String getSchema(Connection conn) {
        try {
            return ConnectionHelp.getPhysicalConnection(conn).getSchema();
        } catch (SQLException e) {

        } finally {
        }
        return null;
    }

    /**
     * 获取catalog不报错
     * @param conn
     * @return
     */
    public static String getCatalog(Connection conn) {
        try {
            return conn.getCatalog();
        } catch (SQLException e) {

        } finally {
        }
        return null;
    }

    /**
     * 将SQL异常构成链表
     * @param errors
     * @return
     */
    public static SQLException wrapExceptions(Collection<SQLException> errors) {
        if (errors == null || errors.isEmpty()) return null;
        Iterator<SQLException> it = errors.iterator();
        SQLException root = it.next();
        SQLException last = root;
        while (it.hasNext()) {
            SQLException current = it.next();
            last.setNextException(current);
            last = current;
        }
        return root;
    }

    private static Pattern countSqlPattern = Pattern.compile("\\s+");
    private static String regex = "(left|right|inner) join (fetch )?\\w+(\\.\\w+)*";
    private static Pattern p = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);

    // 从一个标准的select语句中，生成一个 select count(*) 语句
    public static String getRegexSqlSelectCount(String sql) {
        String countSql = countSqlPattern.matcher(sql).replaceAll(" ");//sql.replaceAll("\\s+", " ");
        int pos = countSql.toLowerCase().indexOf(" from ");
        countSql = countSql.substring(pos);
        pos = countSql.toLowerCase().lastIndexOf(" order by ");
        int lastpos = countSql.toLowerCase().lastIndexOf(')');
        if (pos != -1 && pos > lastpos) {
            countSql = countSql.substring(0, pos);
        }
//        countSql = p.matcher(countSql).replaceAll("");
        countSql = "select count(*) " + countSql;
        return countSql;
    }

    public static String getSimpleSqlSelectCount(String sql) {
        return "select count(*) from (" + sql + ") t";
    }

    /**
     * 获取智能查询总数sql
     * @param sql
     * @return
     */
    public static String getSmartSqlSelectCount(String sql) {
        if (sql.contains(KEEP_ORDERBY)) {
            return getSimpleSqlSelectCount(sql);
        }
        return getRegexSqlSelectCount(sql);
    }

    /**
     * 获取colName所在的行数
     * @param meta       从连接中取出的ResultSetMetaData
     * @param columnName 字段名
     * @return 所在的索引, 如果不存在就抛出异常
     * @throws SQLException 指定的colName找不到
     */
    public static int getColumnIndex(ResultSetMetaData meta, String columnName) throws SQLException {
        if (meta == null) return 0;
        int columnCount = meta.getColumnCount();
        for (int i = 1; i <= columnCount; i++) {
            if (meta.getColumnName(i).equalsIgnoreCase(columnName)) {
                return i;
            }
        }
        logger.info(String.format("Can not find @Column(%s) in table/view (%s)", columnName, meta.getTableName(1)));
        throw new SQLException(String.format("Can not find @Column(%s)", columnName));
    }
}
